﻿using BaseData.CommonLib.Models;
using Dapper;
using Swimj.CommonLib.Base;
using System.Text;
using Microsoft.Data.SqlClient;
using Swimj.DataCore;

namespace BaseData.ApplicationLib.Queries
{
    public class MaterialQueries : IMaterialQueries
    {
        private readonly string? _defaultConnStr;

        public MaterialQueries(string? defaultConnStr)
        {
            _defaultConnStr = defaultConnStr;
        }

        public async Task<Result<IList<MaterialInfoResponseDto>>?> QueryMaterials(MaterialSearchRequestDto model)
        {
            IEnumerable<MaterialInfoResponseDto>? data = null;
            using (var db = new SqlConnection(_defaultConnStr))
            {
                StringBuilder sqlBuilder = new StringBuilder(@"
select Id,
       Code,
       Name,
       Description,
       DataState,
       CreatedBy,
       CreatedOn,
       LastModifiedBy,
       LastModifiedOn,
       DeletedBy,
       DeletedOn,
       MaterialType,
       Specification,
       Unit,
       Sort
from MES_MATERIAL where 1 = 1 ");

                //DynamicParameters parameters = new DynamicParameters();

                //if (model.ParentId != null || model.ParentId > 0)
                //{
                //    sqlBuilder.Append(" and parentId = @parentId");
                //    parameters.Add("parentId", model.ParentId);
                //}

                //sqlBuilder.Append(" limit @Offset,@RowsPerPage ");
                //parameters.Add("Offset", (model.Page - 1) * model.Limit);
                //parameters.Add("RowsPerPage", model.Limit);


                data = await db.QueryAsync<MaterialInfoResponseDto>(sqlBuilder.ToString());
            }

            return await Result<IList<MaterialInfoResponseDto>>.SuccessAsync(data.ToList());
        }

        public async Task<PaginatedResult<MaterialInfoResponseDto>?> QueryMaterialsByPage(
            MaterialSearchRequestDto model)
        {
            IEnumerable<MaterialInfoResponseDto>? data = null;
            int totalCount = 0;
            using (var db = new SqlConnection(_defaultConnStr))
            {
                var query = new Query(db);

                StringBuilder sqlBuilder = new StringBuilder(@"
select material.Id,
       material.Code,
       material.Name,
       material.Description,
       material.DataState,
       material.CreatedBy,
       material.CreatedOn,
       material.LastModifiedBy,
       material.LastModifiedOn,
       material.DeletedBy,
       material.DeletedOn,
       material.MaterialType,
       material.Specification,
       material.Unit,
       material.Sort,
       category.Code CategoryCode,
       category.Name CategoryName,
       unit.Code     UnitCode,
       unit.Name     UnitName
from MES_MATERIAL material
         left join MES_MATERIAL_CATEGORY category on material.MaterialType = category.Id
         left join MES_CALCULATEUNIT unit on material.Unit = unit.Id where 1 = 1 ");

                DynamicParameters parameters = new DynamicParameters();

                //if (model.ParentId != null || model.ParentId > 0)
                //{
                //    sqlBuilder.Append(" and parentId = @parentId");
                //    parameters.Add("parentId", model.ParentId);
                //}

                query.BuildPageQueries(model.Page, model.Limit, sqlBuilder.ToString());

                data = await db.QueryAsync<MaterialInfoResponseDto>(query.SqlPage, parameters);

                totalCount = await db.ExecuteScalarAsync<int>(query.SqCount, parameters);
            }

            return await PaginatedResult<MaterialInfoResponseDto>.SuccessAsync(data.ToList(), totalCount,
                model.Page,
                model.Limit);
        }

        public async Task<Result<MaterialInfoResponseDto>?> GetMaterial(int key)
        {
            MaterialInfoResponseDto? data = null;
            using (var db = new SqlConnection(_defaultConnStr))
            {
                StringBuilder sqlBuilder = new(@"
select Id,
       Code,
       Name,
       Description,
       DataState,
       CreatedBy,
       CreatedOn,
       LastModifiedBy,
       LastModifiedOn,
       DeletedBy,
       DeletedOn,
       MaterialType,
       Specification,
       Unit,
       Sort
from MES_MATERIAL where 1 = 1 and id = @id ");

                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("id", key);

                data = await db.QueryFirstOrDefaultAsync<MaterialInfoResponseDto>(sqlBuilder.ToString(), parameters);
            }

            return await Result<MaterialInfoResponseDto>.SuccessAsync(data);
        }
    }
}